/*
 * # 应用所有数据库表:
 *  Log - 日志
 * 
 * # 描述:
 *  向开发者提供记录数据操作日志消息的服务，同时向系统管理人员提供查看和管理已经记录的数据操作日志消息的功能。
 *  数据操作的日志消息将记录在数据库表中，如果开发者需要调试、跟踪或异常错误等日志消息，应该使用日志文件。
 * 
 * Copyright (c), 上海沃恩信息科技有限公司.
 * All rights reserved.
 * Licensed under BSD (https://www.pittypat.work/bsd.html).
 */

use "pittypat_app";
go

-- 确保架构存在
if schema_id('waln_log') is null
	exec('create schema "waln_log" authorization "dbo"');
go

/*
 * # 辅助函数:
 *  _pittypat_table_exists - 检查指定的表是否已经存在
 * 
 * # 描述:
 *  检查指定的表是否已经存在。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_log"."_pittypat_table_exists"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_log"."_pittypat_table_exists"() returns int begin return 0; end');
go

alter function "waln_log"."_pittypat_table_exists"(@tbl varchar(30))
returns bit
as
begin
	if @tbl is not null and object_id(N'"waln_log".' + @tbl, N'U') is not null
		return 1;
	return 0;
end;
go

/*
 * # 辅助函数:
 *  _pittypat_column_exists - 检查指定的列是否已经存在
 * 
 * # 描述:
 *  检查指定的列是否已经存在。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_log"."_pittypat_column_exists"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_log"."_pittypat_column_exists"() returns int begin return 0; end');
go

alter function "waln_log"."_pittypat_column_exists"(@tbl varchar(30), @col varchar(30))
returns bit
as
begin
	if @tbl is not null and @col is not null and 
		exists (select * from "sys"."columns" where "object_id" = object_id(N'[waln_log].' + @tbl, N'U') and "name" = @col)
		return 1;
	return 0;
end;
go

/*
 * # 辅助函数:
 *  _pittypat_index_exists - 检查指定的索引是否已经存在
 * 
 * # 描述:
 *  检查指定的索引是否已经存在。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_log"."_pittypat_index_exists"') and "type" in (N'FN', N'IF', N'TF', N'FS', N'FT'))
	exec('create function "waln_log"."_pittypat_index_exists"() returns int begin return 0; end');
go

alter function "waln_log"."_pittypat_index_exists"(@tbl varchar(30), @idx varchar(63))
returns bit
as
begin
	if @tbl is not null and @idx is not null and 
		exists (select * from "sys"."indexes" where "object_id" = object_id(N'"waln_log".' + @tbl, N'U') and "name" = @idx)
		return 1;
	return 0;
end;
go

/*
 * # 数据库表 - Sql Server:
 *  OpMessages - 数据操作消息
 * 
 * # 描述:
 *  表示一条已经记录的数据操作的日志消息。
 */

if not exists (select * from "sys"."objects" where "object_id" = object_id(N'"waln_log"."_pittypat_replace_table"') and "type" in (N'P', N'PC'))
	exec('create procedure "waln_log"."_pittypat_replace_table" as begin set nocount on; end');
go

alter procedure "waln_log"."_pittypat_replace_table"
as
begin
	set nocount on;

	if "waln_log"."_pittypat_table_exists"('"OpMessages"') = 0
	begin
		create table "waln_log"."OpMessages"
		(
			"Id" bigint not null identity(1, 1), -- 消息 Id
			"UserId" uniqueidentifier null, -- 用户 Id
			"OpType" tinyint not null, -- 操作类型代码
			"OpTime" datetime2 not null, -- 操作时间
			"TableNames" varchar(200) null, -- 数据库表名称
			"ProcName" varchar(128) not null, -- 存储过程名称
			"Success" bit not null, -- 是否成功
			"Summary" nvarchar(200) null, -- 操作摘要
			"Body" nvarchar(max) null -- 消息正文
		)
		on "primary";

		-- 索引 K_OpMessages
		alter table "waln_log"."OpMessages" add constraint
		"K_OpMessages" primary key clustered
		(
			"Id" asc
		)
		with
		(
			statistics_norecompute = off,
			ignore_dup_key = off,
			allow_row_locks = on,
			allow_page_locks = on
		)
		on "primary";

		-- 索引 I_OpMessages_User
		create nonclustered index "I_OpMessages_User"
		on "waln_log"."OpMessages"
		(
			"UserId" asc
		)
		with
		(
			statistics_norecompute = off,
			ignore_dup_key = off,
			allow_row_locks = on,
			allow_page_locks = on
		)
		on "primary";

		-- 索引 I_OpMessages_Type
		create nonclustered index "I_OpMessages_Type"
		on "waln_log"."OpMessages"
		(
			"OpType" asc
		)
		with
		(
			statistics_norecompute = off,
			ignore_dup_key = off,
			allow_row_locks = on,
			allow_page_locks = on
		)
		on "primary";

		-- 索引 I_OpMessages_Time
		create nonclustered index "I_OpMessages_Time"
		on "waln_log"."OpMessages"
		(
			"OpTime" asc
		)
		with
		(
			statistics_norecompute = off,
			ignore_dup_key = off,
			allow_row_locks = on,
			allow_page_locks = on
		)
		on "primary";
	end
	else
	begin
		-- 列 UserId 
		if "waln_log"."_pittypat_column_exists"('OpMessages', 'UserId') = 0
			alter table "waln_log"."OpMessages" add
			"UserId" uniqueidentifier null;
		else
			alter table "waln_log"."OpMessages" alter column
			"UserId" uniqueidentifier null;

		-- 列 OpType 
		if "waln_log"."_pittypat_column_exists"('OpMessages', 'OpType') = 0
			alter table "waln_log"."OpMessages" add
			"OpType" tinyint not null;
		else
			alter table "waln_log"."OpMessages" alter column
			"OpType" tinyint not null;

		-- 列 OpTime 
		if "waln_log"."_pittypat_column_exists"('OpMessages', 'OpTime') = 0
			alter table "waln_log"."OpMessages" add
			"OpTime" datetime2 not null;
		else
			alter table "waln_log"."OpMessages" alter column
			"OpTime" datetime2 not null;

		-- 列 TableNames 
		if "waln_log"."_pittypat_column_exists"('OpMessages', 'TableNames') = 0
			alter table "waln_log"."OpMessages" add
			"TableNames" varchar(200) null;
		else
			alter table "waln_log"."OpMessages" alter column
			"TableNames" varchar(200) null;

		-- 列 ProcName 
		if "waln_log"."_pittypat_column_exists"('OpMessages', 'ProcName') = 0
			alter table "waln_log"."OpMessages" add
			"ProcName" varchar(128) not null;
		else
			alter table "waln_log"."OpMessages" alter column
			"ProcName" varchar(128) not null;

		-- 列 Success 
		if "waln_log"."_pittypat_column_exists"('OpMessages', 'Success') = 0
			alter table "waln_log"."OpMessages" add
			"Success" bit not null;
		else
			alter table "waln_log"."OpMessages" alter column
			"Success" bit not null;

		-- 列 Summary 
		if "waln_log"."_pittypat_column_exists"('OpMessages', 'Summary') = 0
			alter table "waln_log"."OpMessages" add
			"Summary" nvarchar(200) null;
		else
			alter table "waln_log"."OpMessages" alter column
			"Summary" nvarchar(200) null;

		-- 列 Body 
		if "waln_log"."_pittypat_column_exists"('OpMessages', 'Body') = 0
			alter table "waln_log"."OpMessages" add
			"Body" nvarchar(max) null;
		else
			alter table "waln_log"."OpMessages" alter column
			"Body" nvarchar(max) null;

		-- 删除索引 I_OpMessages_User
		if "waln_log"."_pittypat_index_exists"('OpMessages', 'I_OpMessages_User') = 1
			drop index "I_OpMessages_User" on "waln_log"."OpMessages";

		-- 创建索引 I_OpMessages_User
		create nonclustered index "I_OpMessages_User"
		on "waln_log"."OpMessages"
		(
			"UserId" asc
		)
		with
		(
			statistics_norecompute = off,
			ignore_dup_key = off,
			allow_row_locks = on,
			allow_page_locks = on
		)
		on "primary";

		-- 删除索引 I_OpMessages_Type
		if "waln_log"."_pittypat_index_exists"('OpMessages', 'I_OpMessages_Type') = 1
			drop index "I_OpMessages_Type" on "waln_log"."OpMessages";

		-- 创建索引 I_OpMessages_Type
		create nonclustered index "I_OpMessages_Type"
		on "waln_log"."OpMessages"
		(
			"OpType" asc
		)
		with
		(
			statistics_norecompute = off,
			ignore_dup_key = off,
			allow_row_locks = on,
			allow_page_locks = on
		)
		on "primary";

		-- 删除索引 I_OpMessages_Time
		if "waln_log"."_pittypat_index_exists"('OpMessages', 'I_OpMessages_Time') = 1
			drop index "I_OpMessages_Time" on "waln_log"."OpMessages";

		-- 创建索引 I_OpMessages_Time
		create nonclustered index "I_OpMessages_Time"
		on "waln_log"."OpMessages"
		(
			"OpTime" asc
		)
		with
		(
			statistics_norecompute = off,
			ignore_dup_key = off,
			allow_row_locks = on,
			allow_page_locks = on
		)
		on "primary";
	end
end
go

exec "waln_log"."_pittypat_replace_table";
go

/*
	select
		"UserId", -- 用户 Id
		"OpType", -- 操作类型代码
		"OpTime", -- 操作时间
		"TableNames", -- 数据库表名称
		"ProcName", -- 存储过程名称
		"Success", -- 是否成功
		"Summary", -- 操作摘要
		"Body"  -- 消息正文
	from
		"waln_log"."OpMessages"
	where
		"Id" = @id;

	insert into "waln_log"."OpMessages"
	(
		"UserId", -- 用户 Id
		"OpType", -- 操作类型代码
		"OpTime", -- 操作时间
		"TableNames", -- 数据库表名称
		"ProcName", -- 存储过程名称
		"Success", -- 是否成功
		"Summary", -- 操作摘要
		"Body"  -- 消息正文
	)
	values
	(
		@userId, -- 用户 Id
		@opType, -- 操作类型代码
		@opTime, -- 操作时间
		@tableNames, -- 数据库表名称
		@procName, -- 存储过程名称
		@success, -- 是否成功
		@summary, -- 操作摘要
		@body  -- 消息正文
	);

	update
		"waln_log"."OpMessages"
	set
		"UserId" = @userId, -- 用户 Id
		"OpType" = @opType, -- 操作类型代码
		"OpTime" = @opTime, -- 操作时间
		"TableNames" = @tableNames, -- 数据库表名称
		"ProcName" = @procName, -- 存储过程名称
		"Success" = @success, -- 是否成功
		"Summary" = @summary, -- 操作摘要
		"Body" = @body  -- 消息正文
	where
		"Id" = @id;

	delete from
		"waln_log"."OpMessages"
	where
		"Id" = @id;
*/
